﻿using BCCommon;
using BCCommon.TMS.FreightEnums;
using BCCommon.TMS.Order;
using BCEntity.TMS.FreightConfiguration;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace BCData.TMS.FreightConfiguration
{
    public class FreightConfigurationData : IFreightConfigurationData
    {
        #region 私有变量

        private readonly IDatabaseContext database;
        //private readonly ISqlCommand sqlCommand;

        private const string QUERY = @" SELECT {0} FROM
	                                       freightconfiguration A
	                                       INNER JOIN vehicletype B ON A.VehicleTypeId = B.VehicleTypeId
	                                       LEFT JOIN province P ON A.ProvinceCode = P.ProvinceCode
	                                       LEFT JOIN city C ON A.CityCode = C.CityCode
	                                       LEFT JOIN county CO ON A.CountyCode = CO.CountyCode
	                                       LEFT JOIN (
	                                       SELECT
		                                        A.vehiclecategoryreferenceId,
		                                        A.VehicleTypeId,
		                                        A.VehicleCategory,
		                                        B.ItemValue AS CategoryValue,
		                                        C.VehicleTypeName
	                                        FROM
		                                        vehiclecategoryreference A
		                                        INNER JOIN dictionaryreference B ON A.VehicleCategory = B.SearchKey
	                                            INNER JOIN vehicletype C ON A.VehicleTypeId = C.VehicleTypeId
	                                        ) D ON A.VehicleCategoryReferenceId = D.VehicleCategoryReferenceId";

        private const string FIELD = @"
                                        A.FreightConfigurationId,
	                                    A.VehicleCategoryReferenceId,
	                                    A.ProvinceCode,
	                                    A.CityCode,
	                                    A.CountyCode,
	                                    A.VehicleTypeId,
	                                    A.StartPrice,
	                                    A.StartMileage,
	                                    A.MileagePrice,
	                                    A.CreateTime,
	                                    A.UpdateTime,
	                                    A.Editer,
                                        A.SourceType,
                                        A.FreightType,
                                        C.CityNegotiatedPrice,
	                                    CO.CountyName,
	                                    P.ProvinceName,
	                                    C.CityName,
	                                    B.VehicleTypeName,
	                                    D.VehicleCategoryReferenceId,
	                                    D.CategoryValue";

        private const string QUERY_JOIN = @" SELECT {0} FROM
                                            AgentAreaRecord F
	                                       INNER JOIN FreightConfiguration A ON A.CountyCode = F.CountyCode 
	                                       INNER JOIN vehicletype B ON A.VehicleTypeId = B.VehicleTypeId
	                                       LEFT JOIN province P ON A.ProvinceCode = P.ProvinceCode
	                                       LEFT JOIN city C ON A.CityCode = C.CityCode
	                                       LEFT JOIN county CO ON A.CountyCode = CO.CountyCode
	                                       LEFT JOIN (
	                                       SELECT
		                                        A.vehiclecategoryreferenceId,
		                                        A.VehicleTypeId,
		                                        A.VehicleCategory,
		                                        B.ItemValue AS CategoryValue,
		                                        C.VehicleTypeName
	                                        FROM
		                                        vehiclecategoryreference A
		                                        INNER JOIN dictionaryreference B ON A.VehicleCategory = B.SearchKey
	                                            INNER JOIN vehicletype C ON A.VehicleTypeId = C.VehicleTypeId
	                                        ) D ON A.VehicleCategoryReferenceId = D.VehicleCategoryReferenceId";

        #endregion 私有变量

        #region 构造函数

        public FreightConfigurationData(//ISqlCommand sqlCommand, 
            IDatabaseContext database)
        {
            //this.sqlCommand = sqlCommand;
            this.database = database;
        }

        #endregion 构造函数

        #region 公共方法

        public async Task<int> InsertAllAsync(List<FreightConfigurationEntity> entitys)
        {
            //var sql = SqlCommands.GetInsertSql("freightconfiguration",
            //    new[]
            //    {
            //        "ProvinceCode", "CityCode", "CountyCode", "VehicleTypeId","VehicleCategoryReferenceId", "StartPrice", "StartMileage",
            //        "MileagePrice", "Editer"
            //    });

            //var res = await this.sqlCommand.ExecuteMultipleAsync(sql, CommandType.Text, entitys);
            //return res.ToInt32();
            return await database.Master.InsertAllAsync(entitys);
        }

        public async Task<int> UpdateAsync(FreightConfigurationEntity entity)
        {
            var sql = SqlCommands.GetUpdateSql("freightconfiguration", new[]
            {
                "ProvinceCode", "CityCode", "CountyCode", "StartPrice", "StartMileage",
                "MileagePrice", "Editer", "UpdateTime"
            }, new[] { "FreightConfigurationId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ProvinceCode", entity.ProvinceCode, DbType.Int32);
            parameters.Add("?CityCode", entity.CityCode, DbType.Int32);
            parameters.Add("?CountyCode", entity.CountyCode, DbType.Int32);
            parameters.Add("?StartPrice", entity.StartPrice, DbType.Decimal);
            parameters.Add("?StartMileage", entity.StartMileage, DbType.Decimal);
            parameters.Add("?MileagePrice", entity.MileagePrice, DbType.Decimal);
            parameters.Add("?Editer", entity.Editer, DbType.AnsiString, size: 20);
            parameters.Add("?UpdateTime", entity.UpdateTime, DbType.DateTime);
            parameters.Add("?FreightConfigurationId", entity.FreightConfigurationId, DbType.Int32);
            //return await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return await database.Master.ExecuteAsync(sql, parameters);
        }

        public async Task<int> DeleteAsync(int freightConfigurationId)
        {
            string sql = "DELETE FROM freightconfiguration WHERE FreightConfigurationId = ?FreightConfigurationId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightConfigurationId", freightConfigurationId, DbType.Int32);
            //return await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return await database.Master.ExecuteAsync(sql, parameters);
        }

        public async Task<FreightConfigurationExpandEntity> GetByIdAsync(int freightConfigurationId)
        {
            var sql = string.Format("{0} WHERE FreightConfigurationId=?FreightConfigurationId", string.Format(QUERY, FIELD));
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightConfigurationId", freightConfigurationId, DbType.Int32);
            //var data = await databaseContext.Master.QueryAsync<FreightConfigurationExpandEntity>(sql, parameters);

            //return data.FirstOrDefault();
            return await database.Slave.QueryFirstOrDefaultAsync<FreightConfigurationExpandEntity>(sql, parameters);
        }

        public async Task<FreightConfigurationEntity> GetFreight(int? vehicleTypeId, int? vehicleCategoryReferenceId, string ProvinceCode, string CityCode, string CountyCode, OrderSourceType? sourceType, FreightType? freightType)
        {
            var wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (vehicleTypeId.HasValue)
            {
                wheres.Add(" A.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", vehicleTypeId);
            }
            if (vehicleCategoryReferenceId.HasValue)
            {
                wheres.Add(" A.VehicleCategoryReferenceId=?VehicleCategoryReferenceId");
                parameters.Add("?VehicleCategoryReferenceId", vehicleCategoryReferenceId);
            }
            if (!string.IsNullOrWhiteSpace(ProvinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", ProvinceCode);
            }
            if (!string.IsNullOrWhiteSpace(CityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", CityCode);
            }
            if (!string.IsNullOrWhiteSpace(CountyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", CountyCode);
            }
            if (sourceType.HasValue)
            {
                wheres.Add("A.SourceType = ?SourceType");
                parameters.Add("?SourceType", sourceType.Value.ToString(), DbType.AnsiString);
            }
            if (freightType.HasValue)
            {
                wheres.Add("A.FreightType = ?FreightType");
                parameters.Add("?FreightType", freightType.Value.ToString(), DbType.AnsiString);
            }
            var sql = string.Format("{0} {1} LIMIT 1", string.Format(QUERY, FIELD), (wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : ""));
            //return databaseContext.Master.QueryFirstOrDefaultAsync<FreightConfigurationEntity>(sql, parameters);
            return await database.Slave.QueryFirstOrDefaultAsync<FreightConfigurationEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<FreightConfigurationExpandEntity>, int)> GetAsync(long? agentRecordId, FreightConfigurationViewEntity viewEntity)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                wheres.Add("F.AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (viewEntity.VehicleTypeId.HasValue)
            {
                wheres.Add("A.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", viewEntity.VehicleTypeId);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.ProvinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", viewEntity.ProvinceCode);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.CityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", viewEntity.CityCode);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.CountyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", viewEntity.CountyCode);
            }
            if (viewEntity.SourceType.HasValue)
            {
                wheres.Add("A.SourceType = ?SourceType");
                parameters.Add("?SourceType", viewEntity.SourceType.Value.ToString(), DbType.AnsiString);
            }
            if (viewEntity.FreightType.HasValue)
            {
                wheres.Add("A.FreightType = ?FreightType");
                parameters.Add("?FreightType", viewEntity.FreightType.Value.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} LIMIT {2},{3};
                                         {4}  {1}",
                string.Format(agentRecordId.HasValue ? QUERY_JOIN : QUERY, FIELD),
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                (viewEntity.PageIndex - 1) * viewEntity.PageSize,
                viewEntity.PageSize,
                string.Format(agentRecordId.HasValue ? QUERY_JOIN : QUERY, "COUNT(1)"));
            //var data = await databaseContext.Slave.QueryMultipleAsync<FreightConfigurationExpandEntity, int>(sql, parameters);

            //return (data.Item2, data.Item1.ToList());
            return await database.Slave.QueryPageableListAsync<FreightConfigurationExpandEntity>(sql, parameters);

        }

        public async Task<(IEnumerable<FreightConfigurationExpandEntity>, int)> QeuryAsync(FreightConfigurationViewEntity viewEntity)
        {
            List<string> wheres = new List<string>();

            DynamicParameters parameters = new DynamicParameters();

            if (viewEntity.VehicleTypeId.HasValue)
            {
                wheres.Add("A.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", viewEntity.VehicleTypeId);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.ProvinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", viewEntity.ProvinceCode);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.CityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", viewEntity.CityCode);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.CountyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", viewEntity.CountyCode);
            }

            string sql = string.Format(@"{0} {1} LIMIT {2},{3};
                                         SELECT COUNT(1) FROM freightconfiguration A {1}",
                QUERY,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                viewEntity.PageIndex,
                viewEntity.PageSize);
            sql = string.Format(sql, FIELD);
            //return sqlCommand.QueryMultipleAsync<FreightConfigurationExpandEntity, int>(sql, CommandType.Text, parameters);
            return await database.Slave.QueryPageableListAsync<FreightConfigurationExpandEntity>(sql, parameters);
        }

        public async Task<int> CheckFreightConfigurationExists(int freightConfigId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            wheres.Add(" FreightConfigurationId=?FreightConfigurationId");
            parameters.Add("?FreightConfigurationId", freightConfigId);
            var sql = string.Format("SELECT 1 FROM FreightConfiguration  {0}", (wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : ""));
            var data = await this.database.Master.ExecuteScalarAsync(sql, parameters);
            return data.ToInt32();
        }

        public async Task<int> CheckFreightConfigurationExists(int vehicleTypeId, int vehicleCategoryReferenceId, string provinceCode, string cityCode, string countyCode,
            OrderSourceType sourceType, FreightType freightType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add(" A.VehicleTypeId=?VehicleTypeId");
            parameters.Add("?VehicleTypeId", vehicleTypeId);
            wheres.Add(" A.VehicleCategoryReferenceId=?VehicleCategoryReferenceId");
            parameters.Add("?VehicleCategoryReferenceId", vehicleCategoryReferenceId);
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode);
            }
            wheres.Add("A.SourceType = ?SourceType");
            parameters.Add("?SourceType", sourceType.ToString(), DbType.AnsiString);
            wheres.Add("A.FreightType = ?FreightType");
            parameters.Add("?FreightType", freightType.ToString(), DbType.AnsiString);
            var sql = string.Format("SELECT 1 FROM FreightConfiguration A {0}", (wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : ""));
            //var data = await sqlCommand.ExecuteScalarAsync(sql, CommandType.Text, parameters);
            var data = await database.Slave.ExecuteScalarAsync(sql, parameters);
            return data.ToInt32();
        }
        #endregion 公共方法

        public async Task<int> InsertManyRowAsync(FreightConfigurationEntity freightEntity)
        {
            string sql = @"INSERT INTO FreightConfiguration (ProvinceCode,CityCode,CountyCode,VehicleTypeId,VehicleCategoryReferenceId,StartPrice,StartMileage,MileagePrice,Editer) 
                        SELECT ?ProvinceCode,?CityCode,?CountyCode,VehicleTypeId,VehicleCategoryReferenceId,?StartPrice,?StartMileage,?MileagePrice,?Editer FROM VehicleCategoryReference 
                        WHERE Enabled = 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ProvinceCode", freightEntity.ProvinceCode, DbType.AnsiString);
            parameters.Add("?CityCode", freightEntity.CityCode, DbType.AnsiString);
            parameters.Add("?CountyCode", freightEntity.CountyCode, DbType.AnsiString);
            parameters.Add("?StartPrice", freightEntity.StartPrice, DbType.Decimal);
            parameters.Add("?StartMileage", freightEntity.StartMileage, DbType.Int32);
            parameters.Add("?MileagePrice", freightEntity.MileagePrice, DbType.Decimal);
            parameters.Add("?Editer", freightEntity.Editer, DbType.AnsiString);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction);
        }

        public async Task<IEnumerable<string>> GetCountyCodeList()
        {
            return await database.Slave.QueryAsync<string>("SELECT DISTINCT(CountyCode) FROM FreightConfiguration");
        }

        public async Task<int> InsertReparir(string tempCountyCode, string countyCode)
        {
            return await database.Master.ExecuteAsync(@"INSERT INTO FreightConfiguration(ProvinceCode,CityCode,CountyCode,VehicleTypeId,StartPrice,StartMileage,
														MileagePrice,CreateTime,UpdateTime,Editer,VehicleCategoryReferenceId,SourceType,FreightType)
                            SELECT B.ProvinceCode,B.CityCode,B.CountyCode,A.VehicleTypeId,A.StartPrice,
                            A.StartMileage,A.MileagePrice,NOW(),NOW(),'System',A.VehicleCategoryReferenceId,A.SourceType,A.FreightType   
                            FROM FreightConfiguration A,County B 
                            WHERE A.CountyCode = ?CountyCode AND B.CountyCode = ?TempCountyCode", new { countyCode, tempCountyCode });
        }

        public async Task<IDictionary<int, int>> GetVehicleTypeIdAndReferenceIdList()
        {
            return await database.Slave.QueryDictionaryAsync<int, int>("SELECT DISTINCT(VehicleCategoryReferenceId),VehicleTypeId FROM FreightConfiguration");
        }

        public async Task<int> InsertReparir(KeyValuePair<int, int> keyValuePair, int vehicleCategoryReferenceId, int vehicleTypeId)
        {
            return await database.Master.ExecuteAsync(@"INSERT INTO FreightConfiguration(ProvinceCode,CityCode,CountyCode,VehicleTypeId,StartPrice,StartMileage,
														MileagePrice,CreateTime,UpdateTime,Editer,VehicleCategoryReferenceId,SourceType,FreightType)
                            SELECT A.ProvinceCode,A.CityCode,A.CountyCode,B.VehicleTypeId,A.StartPrice,
                            A.StartMileage,A.MileagePrice,NOW(),NOW(),'System',B.VehicleCategoryReferenceId,A.SourceType,A.FreightType
                            FROM FreightConfiguration A,
                            VehicleCategoryReference B 
                            WHERE A.VehicleTypeId = ?VehicleTypeId AND A.VehicleCategoryReferenceId = ?VehicleCategoryReferenceId 
                            AND B.VehicleCategoryReferenceId = ?TempVehicleCategoryReferenceId AND B.VehicleTypeId = ?TempVehicleTypeId;",
                            new
                            {
                                VehicleTypeId = keyValuePair.Value,
                                VehicleCategoryReferenceId = keyValuePair.Key,
                                TempVehicleCategoryReferenceId = vehicleCategoryReferenceId,
                                TempVehicleTypeId = vehicleTypeId
                            });
        }

        public async Task<IEnumerable<FreightConfigurationEntity>> GetList(long? agentRecordId, string provinceCode, string cityCode, string countyCode, int? vehicleTypeId, OrderSourceType? sourceType, FreightType? freightType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                wheres.Add("F.AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode,DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode,DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode,DbType.AnsiString);
            }
            if (sourceType.HasValue)
            {
                wheres.Add("A.SourceType = ?SourceType");
                parameters.Add("?SourceType", sourceType.Value.ToString(), DbType.AnsiString);
            }
            if (freightType.HasValue)
            {
                wheres.Add("A.FreightType = ?FreightType");
                parameters.Add("?FreightType", freightType.Value.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} ",
                string.Format(agentRecordId.HasValue ? QUERY_JOIN : QUERY, FIELD),
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await database.Slave.QueryAsync<FreightConfigurationEntity>(sql, parameters);
        }
    }
}